package com.zhiche.lisa.bms.service.utils.common;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.*;

/**
 * excel 工具类
 */
public class ExcelUtil extends ExcelCommon {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 导出excel
     *
     * @param fileName  excel名称
     * @param titles    表头
     * @param list      数据
     * @param mappering 映射关系
     */
    public static HSSFWorkbook export(String fileName, String[] titles,
                                      List<?> list, Map<String, Object> mappering) {

        // 新建excel报表
        HSSFWorkbook excel = new HSSFWorkbook();
        // 获取样式
        HSSFCellStyle style = createCellStyle(excel);
        // 添加一个sheet
        HSSFSheet hssfSheet = excel.createSheet(fileName);


        // 创建表头
        HSSFRow titleRow = hssfSheet.createRow(0);
        for (int i = 0; titles.length > i; i++) {
            String title = titles[i];
            // 第一行创建第一个单元格
            HSSFCell hssfCell = titleRow.createCell(i);
            // 设置第一个单元格的值
            hssfCell.setCellValue(title);
            // 设置表头样式
            hssfCell.setCellStyle(style);
            // 设置宽度
            hssfSheet.setColumnWidth(i, (int) 35.7 * 100);
        }

        // 添加内容
        int num = 1;
        for (Object obj : list) {
            HSSFRow contentRow = hssfSheet.createRow(num);

            // 获取obj的所有属性
            List<String> attributeList = getAttributeName(obj);
            // 获取映射map的所有健值
            List<String> keyList = getMapKey(mappering);
            // 获取映射map的所有值
            List<String> valList = getMapVal(mappering);

            for (int i = 0; titles.length > i; i++) {
                for (int j = 0; valList.size() > j; j++) {
                    if (titles[i].trim().equals(valList.get(j).trim())) {
                        HSSFCell contentCell = contentRow.createCell(i);
                        contentCell.setCellValue(getFieldValueByFieldName(obj, keyList.get(j)).toString());
                    }
                }
            }

            num++;
        }
        return excel;
    }

    public static HSSFWorkbook export(String fileName, String[] titles,
                                      List<?> list, Map<String, Object> mappering, List<String> excludes) {

        int count = list.size();
        int size = count % 50000;
        int num1;
        if (size == 0) {
            num1 = count / 50000;
        } else {
            num1 = count / 50000 + 1;
        }

        // 新建excel报表
        HSSFWorkbook excel = new HSSFWorkbook();
        // 获取样式
        HSSFCellStyle style = createCellStyle(excel);
        // 内容样式
        HSSFCellStyle contextstyle = excel.createCellStyle();


        for (int k = 1; k <= num1; k++) {
            // 添加一个sheet
            HSSFSheet hssfSheet = excel.createSheet(fileName + k);

            // 创建表头
            HSSFRow titleRow = hssfSheet.createRow(0);
            for (int i = 0; titles.length > i; i++) {
                String title = titles[i];
                // 第一行创建第一个单元格
                HSSFCell hssfCell = titleRow.createCell(i);
                // 设置第一个单元格的值
                hssfCell.setCellValue(title);
                // 设置表头样式
                hssfCell.setCellStyle(style);
                // 设置宽度
                hssfSheet.setColumnWidth(i, (int) 35.7 * 100);
            }

            // 添加内容
            for (int i = 50000 * k - 50000; i < 50000 * k && i < count; i++) {
                HSSFRow contentRow = hssfSheet.createRow(50001 + i - 50000 * k);
                Object obj = list.get(i);

                // 获取obj的所有属性
                List<String> attributeList = getAttributeName(obj);
                // 获取映射map的所有健值
                List<String> keyList = getMapKey(mappering);
                // 获取映射map的所有值
                List<String> valList = getMapVal(mappering);

                String dateFormate = "yyyy-MM-dd";

                for (int m = 0; titles.length > m; m++) {
                    for (int j = 0; valList.size() > j; j++) {
                        if (titles[m].trim().equals(valList.get(j).trim())) {
                            HSSFCell contentCell = contentRow.createCell(m);
                            Object val = getFieldValueByFieldName(obj, keyList.get(j), dateFormate);

                            if (excludes.contains(keyList.get(j))) {
                                // 设置单元格格式
                                contentCell.setCellStyle(contextstyle);
                                // 设置单元格内容为double类型
                                contentCell.setCellValue(val.toString());
                            } else {
                                //判断data是否为数值型
                                boolean isNum = val.toString().matches("^(-?\\d+)(\\.\\d+)?$");
                                //判断data是否为整数（小数部分是否为0）
                                boolean isInteger = val.toString().matches("^[-\\+]?[\\d]*$");
                                //判断data是否为百分数（是否包含“%”）
                                boolean isPercent = val.toString().contains("%");

                                if (isNum && !isPercent) {
                                    if (isInteger) {
                                        contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));//数据格式只显示整数
                                    } else {
                                        contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
                                    }
                                    // 设置单元格格式
                                    contentCell.setCellStyle(contextstyle);
                                    // 设置单元格内容为double类型
                                    contentCell.setCellValue(Double.parseDouble(val.toString()));
                                } else {
                                    contentCell.setCellStyle(contextstyle);
                                    // 设置单元格内容为字符型
                                    contentCell.setCellValue(val.toString());
                                }
                            }

                        }
                    }
                }

            }
        }

        return excel;
    }

    public static String read(Workbook workbook) {
        Map<String, Object> workBookMap = new HashMap<>();
        List<Map<String, Object>> list = null;
        Row head = null;
        Row row = null;
        if (workbook != null) {
            //用来存放表中数据
            list = new ArrayList<Map<String, Object>>();
            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                return null;
            }
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();

            //获取第一行
            head = sheet.getRow(0);
            if (head != null) {
                Map<Integer, String> headMap = new HashMap<>();
                //获取最大列数
                int colnum = head.getPhysicalNumberOfCells();

                String headData = null;
                for (int j = 0; j < colnum; j++) {
                    headData = (String) getCellFormatValue(head.getCell(j));
                    headMap.put(j, headData);
                }


                for (int i = 1; i <= rownum; i++) {
                    Map<String, Object> map = new LinkedHashMap<String, Object>();
                    row = sheet.getRow(i);

                    Object cellData = null;
                    if (row != null) {
                        for (int j = 0; j < colnum; j++) {
                            cellData = getCellFormatValue(row.getCell(j));
                            map.put(headMap.get(j), cellData);
                        }
                    } else {
                        break;
                    }
                    list.add(map);
                }
            }
            workBookMap.put("rows", list);
        }
        return JSONObject.toJSONString(workBookMap);
    }


    public static SXSSFWorkbook exportSXSSWorkExcel(String fileName, String[] titles,
                                                    List<?> list, Map<String, Object> mappering, List<String> excludes) {

        int count = list.size();
        int size = count % 50000;
        int num1;
        if (size == 0) {
            num1 = count / 50000;
        } else {
            num1 = count / 50000 + 1;
        }

        // 新建excel报表
        SXSSFWorkbook excel = new SXSSFWorkbook(10000);
        // 获取样式
        CellStyle style = createCellStyleBySXSSWork(excel);
        // 内容样式
        CellStyle contextstyle = excel.createCellStyle();


        for (int k = 1; k <= num1; k++) {
            // 添加一个sheet
            SXSSFSheet hssfSheet = excel.createSheet(fileName + k);

            // 创建表头
            SXSSFRow titleRow = hssfSheet.createRow(0);
            for (int i = 0; titles.length > i; i++) {
                String title = titles[i];
                // 第一行创建第一个单元格
                SXSSFCell sfcell = titleRow.createCell(i);
                // 设置第一个单元格的值
                sfcell.setCellValue(title);
                // 设置表头样式
                sfcell.setCellStyle(style);
                // 设置宽度
                hssfSheet.setColumnWidth(i, (int) 35.7 * 100);
            }

            // 添加内容
            for (int i = 50000 * k - 50000; i < 50000 * k && i < count; i++) {
                SXSSFRow contentRow = hssfSheet.createRow(50001 + i - 50000 * k);
                Object obj = list.get(i);

                // 获取obj的所有属性
                getAttributeName(obj);
                // 获取映射map的所有健值
                List<String> keyList = getMapKey(mappering);
                // 获取映射map的所有值
                List<String> valList = getMapVal(mappering);

                String dateFormate = "yyyy-MM-dd";

                for (int m = 0; titles.length > m; m++) {
                    for (int j = 0; valList.size() > j; j++) {
                        if (titles[m].trim().equals(valList.get(j).trim())) {
                            SXSSFCell contentCell = contentRow.createCell(m);
                            Object val = getFieldValueByFieldName(obj, keyList.get(j), dateFormate);

                            if (excludes.contains(keyList.get(j))) {
                                // 设置单元格格式
                                contentCell.setCellStyle(contextstyle);
                                // 设置单元格内容为double类型
                                contentCell.setCellValue(val.toString());
                            } else {
                                //判断data是否为数值型
                                boolean isNum = val.toString().matches("^(-?\\d+)(\\.\\d+)?$");
                                //判断data是否为整数（小数部分是否为0）
                                boolean isInteger = val.toString().matches("^[-\\+]?[\\d]*$");
                                //判断data是否为百分数（是否包含“%”）
                                boolean isPercent = val.toString().contains("%");

                                if (isNum && !isPercent) {
                                    if (isInteger) {
                                        contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));//数据格式只显示整数
                                    } else {
                                        contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
                                    }
                                    // 设置单元格格式
                                    contentCell.setCellStyle(contextstyle);
                                    // 设置单元格内容为double类型
                                    contentCell.setCellValue(Double.parseDouble(val.toString()));
                                } else {
                                    contentCell.setCellStyle(contextstyle);
                                    // 设置单元格内容为字符型
                                    contentCell.setCellValue(val.toString());
                                }
                            }

                        }
                    }
                }

            }
        }

        return excel;
    }

    public static Workbook getWorkBookByMultipartFile(MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();
        String extString = originalFilename.substring(originalFilename.lastIndexOf("."));
        if (".xls".equals(extString)) {
            return new HSSFWorkbook(file.getInputStream());
        } else if (".xlsx".equals(extString)) {
            return new XSSFWorkbook(file.getInputStream());
        }
        return null;
    }
}